﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using Ebiz.libs;

namespace Ebiz
{
    public partial class Home : System.Web.UI.Page
    {
        public int TotalProc = 0;
        public string CountDelay = "0";
        public string TblProcess = "";

        public string sqry = "";
        public enum CLStatus { 
            INPROC, RFI, REJECTED, CLOSED
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            CheckStatusCode();
            TotalProc = TotalProcess(Func.GetUserInfo(Func.ShowInfo._UserID), CLStatus.INPROC);
            CountDelay = TotalProc.ToString();
            TblProcess = TableProcess(Func.GetUserInfo(Func.ShowInfo._UserID), CLStatus.INPROC);
            //strMsg = Func.GetUserInfo(Func.ShowInfo._SSID);

            /*
            string qryWhere = "((StatusCode = '100') OR (StatusCode = '101') " +
                        "OR (StatusCode = '200') OR (StatusCode = '203'))";
            sqry = "SELECT CLNum, Token, DateRequest, ApprovalCurrent, CreatedBy, StatusCode, CLCustName " +
                "FROM Ebiz.dbo.tbl_Request WHERE " + qryWhere + " AND " +
                "((CreatedBy = '" + Func.GetUserInfo(Func.ShowInfo._UserID) + 
                "') OR (ApprovalCurrent = '" + Func.GetUserInfo(Func.ShowInfo._UserID) + "')) " +
                "ORDER BY DateRequest DESC, CLNum ASC";
            */
        }

        private static int TotalProcess(string UserID, CLStatus RequestStatus){
            int result = 0;

            string qryWhere = "";
            switch (RequestStatus) { 
                case CLStatus.INPROC:
                    /*qryWhere = "((StatusCode = '100') OR (StatusCode = '101') " +
                        "OR (StatusCode = '200') OR (StatusCode = '203')";*/
                    qryWhere = "(StatusCode IN('100','101','200','203','110','111','210','213'))";
                    break;
                case CLStatus.RFI:
                    qryWhere = "(StatusCode = '203')";
                    break;
            }
            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT COUNT(CLNum) FROM " + dbName + ".dbo.tbl_Request WHERE " + qryWhere +
                " AND ((CreatedBy = @CreatedBy) OR (ApprovalCurrent = @ApprovalCurrent))";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();

                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CreatedBy", UserID);
                    cmd.Parameters.AddWithValue("@ApprovalCurrent", UserID);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()){
                            result = Convert.ToInt32(rdr.GetValue(0));
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static string TableProcess(string UserID, CLStatus RequestStatus) {
            string result = "";

            string qryWhere = "";
            switch (RequestStatus){
                case CLStatus.INPROC:
                    /*qryWhere = "((StatusCode = '100') OR (StatusCode = '101') " +
                        "OR (StatusCode = '200') OR (StatusCode = '203'))";*/
                    qryWhere = "(StatusCode IN('100','101','200','203','110','111','210','213'))";
                    break;
                case CLStatus.RFI:
                    qryWhere = "(StatusCode = '203')";
                    break;
            }
            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT CLNum, Token, DateRequest, ApprovalCurrent, CreatedBy, StatusCode, CLCustName " + 
                "FROM " + dbName + ".dbo.tbl_Request WHERE " + qryWhere + " AND " + 
                "((CreatedBy = @CreatedBy) OR (ApprovalCurrent = @ApprovalCurrent)) " +
                "ORDER BY DateRequest DESC, CLNum ASC";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();

                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CreatedBy", UserID);
                    cmd.Parameters.AddWithValue("@ApprovalCurrent", UserID);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        int idx = 0;

                        string TRow = "<tr";
                        string TREnd = "</tr>\n";
                        string TDStart = "<td>";
                        string TDLeft = "<td style=\"text-align:left;\">";
                        string TDEnd = "</td>";
                        while (rdr.Read()){
                            idx++;

                            string StrQry = "id=" + rdr.GetValue(0).ToString() + "&atdn=" + rdr.GetValue(1).ToString();
                            string pgView = "";
                            string tmpStatus = rdr.GetValue(5).ToString();
                            switch (tmpStatus) { 
                                case "100":
                                case "101":
                                case "200":
                                case "203":
                                    pgView = "ViewRequest";
                                    break;
                                case "110":
                                case "111":
                                case "210":
                                case "213":
                                    pgView = "BulkView";
                                    break;
                            }

                            string LinkTo = pgView + ".aspx?cl=" + CryptLib.Base64(StrQry, CryptMode.ENCRYPT);
                            string btnView = "<a class=\"btnSmall\" title=\"View Details\" style=\"margin:2px 0;padding:1px 9px;\" href=\"" + 
                                LinkTo + "\">View</a>";

                            string rowClass = ((idx % 2) == 1) ? " class=\"odd\"" : " class=\"even\"";
                            string TRStart = TRow + rowClass + ">";

                            string _CustCode = rdr.GetValue(0).ToString();
                            string _CSCodeAZI = rdr.GetValue(1).ToString();
                            string _CustName = rdr.GetValue(2).ToString();
                            string _CustAddress = rdr.GetValue(3).ToString();

                            string[] tmpSDate = (rdr.GetValue(2).ToString()).Split(' ');
                            string tmpDate = tmpSDate[0];
                            string DateRequest = Func.FormatDate(tmpDate, Func.DTFormat.MDYtoDMY, "/", "-");
                            
                            
                            string tmpResult = TRStart + TDStart + idx.ToString() + TDEnd;
                            tmpResult += TDLeft + rdr.GetValue(0).ToString() + TDEnd;
                            tmpResult += TDStart + DateRequest + TDEnd;
                            tmpResult += TDLeft + rdr.GetValue(6).ToString() + TDEnd;
                            tmpResult += TDStart + PRStatus(rdr.GetValue(5).ToString()) + TDEnd;
                            tmpResult += TDStart + btnView + TREnd;

                            result += tmpResult;
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }
        private void CheckStatusCode()
        {
            string strQuery = string.Empty;
            if (isDataExist("StatusCode", "210", "[dbo].[mstStatusCode]") == false)
            {
                ExecQuery("INSERT INTO [dbo].[mstStatusCode] (StatusCode,Description) Values('210','Waiting for Approval (Bulk)')");
                
            }
            if(isDataExist("StatusCode", "211", "[dbo].[mstStatusCode]") == false)
            {
                ExecQuery("INSERT INTO [dbo].[mstStatusCode] (StatusCode,Description) Values('211','Approved (Bulk)')");
            }
            if(isDataExist("StatusCode", "213", "[dbo].[mstStatusCode]") == false)
            {
                ExecQuery("INSERT INTO [dbo].[mstStatusCode] (StatusCode,Description) Values('213','Return for Information (Bulk)')");
            }
            if(isDataExist("StatusCode", "214", "[dbo].[mstStatusCode]") == false)
            {
                ExecQuery("INSERT INTO [dbo].[mstStatusCode] (StatusCode,Description) Values('214','Rejected (Bulk)')");
            }
           
            
        }
        private void ExecQuery(string strQuery)
        {
            string DBName = DBUtilities.GetDBName("devCon");
            string strCon = DBUtilities.strConnection("devCon");
            SqlConnection cn = new SqlConnection(strCon);
            using (cn)
            {
                try
                {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(strQuery, cn);
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    string errMsg = e.Message;
                }
                finally
                {
                    if (cn.State == ConnectionState.Open)
                    {
                        cn.Close();
                    }
                }
            }
        }
        private static string PRStatus(string StatusCode) {
            Dictionary<string, string> dctStatus = Func.GetStatusInfo(StatusCode);
            return dctStatus["DESCRIPTION"];
        }
        protected static bool isDataExist(string FieldName, object FieldValue, string TableName)
        {
            bool result = false;

            string DBName = DBUtilities.GetDBName("devCon");
            string strCon = DBUtilities.strConnection("devCon");
            SqlConnection cn = new SqlConnection(strCon);
            using (cn)
            {
                try
                {
                    cn.Open();

                    string sql = "SELECT COUNT(" + FieldName + ") FROM " + DBName + ".dbo." + TableName +
                        " WHERE (" + FieldName + " = @" + FieldName + ")";
                    SqlCommand cmd = new SqlCommand(sql, cn);
                    cmd.Parameters.AddWithValue(FieldName, FieldValue);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows)
                    {
                        if (rdr.Read())
                        {
                            int DataCount = Convert.ToInt32(rdr.GetValue(0).ToString());
                            if (DataCount > 0)
                            {
                                result = true;
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    string errMsg = ex.Message;
                }
                finally
                {
                    if (cn.State == ConnectionState.Open)
                    {
                        cn.Close();
                    }
                }
            }
            return result;
        }
    }
}
